how can i VLOOKUP multiple rows in single column?

[sand takagi]

in sheet1, i put dropbox in b5, then im planning to have VLOOKUP in c5. all the data is acquired from sheet2.
i.e: if i choose 'payment voucher' in the drop box then the value in c5 should be data from sheet2, B3 until B8.

already try to use VLOOKUP formula but it only shows the fisrt row only.

thanks in advance

adnan

[jeffreybrown]

Are you looking for this?

[:) Sixthsense :)]

In Sheet2 fill the A4 to A8 with the above data (i.e. PAYMENT VOUCHER) and like the same From A10 to A12 fill MIX DOCUMENT 1.

In C5 cell of Sheet1- Array Formula - Requires Ctrl+Shift+Ent

Please Login or Register  to view this content.

Drag it down

[sand takagi]

Are you looking for this?

thanks for your quick response, however, its not as i want.. when i choose 'payment voucher' in b5 (the drop box), cell c5 should automatically came out with data from sheet2!b3:b8 (data starting from b3 to b8)

[jeffreybrown]

@sand takagi,

Sorry I read your requirements wrong, but hopefully you try Sixthsenses' suggestions as it appears to do exactly as you wish.

[sand takagi]

i think i didnt explain it very clear. data from b3 to b8 of sheet2 is under one category which is 'payment voucher' therefore if i choose 'payment voucher' in b5 of sheet1, it should listing down all data from sheet2!b3:b8. that is why i try using VLOOKUP coz it almost as i want the only problem is, its only shows first row of b3. not by entire to b8 of sheet2.

[sand takagi]

@sand takagi,

Sorry I read your requirements wrong, but hopefully you try Sixthsenses' suggestions as it appears to do exactly as you wish.

sorry making u confused. mind my english pls. i dont understand what does u mean by this "hopefully you try Sixthsenses' suggestions as it appears to do exactly as you wish. "

[jeffreybrown]

Is this what you want?

[:) Sixthsense :)]

@ jeffreybrown,

Tricky Solution

I was surprised how the formula is running when the cells are seems to be nothing. When I place the cursor then I realized your better approach. Nice work

[jeffreybrown]

@Sixthsense,

It's all about team work

[sand takagi]

Is this what you want?

wow amazing job!! appreciate your effort... awesome man. just a little bit more (sorry for asking more), how if i want to add more data in sheet2. i mean more category.

[jeffreybrown]

@sand takagi,

Ok see how this goes for you (atachment updated in post #8).

Building on the formula Sixthsense offered in post #2, updated the formula to use the first two named ranges below. These ranges are going to expand and/or contract based on the text entered in Sheet2 column A/B. Important, based on text. If the ranges will include numbers then some updates will be required.

Dynamic Named Ranges (Ctrl + F3)

• FILE_NAME >> =Sheet2!$A$3:INDEX(Sheet2!$A:$A,MATCH(REPT("z",255),Sheet2!$A:$A))
• FILE_NUMBER >> =Sheet2!$B$3:INDEX(Sheet2!$B:$B,MATCH(REPT("z",255),Sheet2!$B:$B))
• MyDV >> =Sheet2!$E$2:INDEX(Sheet2!$E:$E,MATCH(REPT("z",255),Sheet2!$E:$E))

On Sheet2, column E is where the data validation (DV) list comes from for cell B5 on Sheet1. The DV uses the last named range from above. As soon as you enter a new File Name in Sheet2 column A, that unique value will be transferred via a formula to column E. Drag the formula down as far as required, but for now, it goes down as far as the borders.

There is also some conditional formatting in column E which is masking the #N/As produced by the formula but these are only required so the DV does not pick up the blank rows.

Same applies for Sheet1 column C. Drag the formula down as far as required, but for now, it goes down as far as the borders.

IMPORTANT

• All of these formulas are array formulas
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

If you have any more questions or something was left out please let us know.