vlooup input 1 and output 2

Hi,Team

Problem with vlookup ,
I have attached excel workbook with input and output.

hi Visha, try this array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Vlookup doesn't give you multiple return options. Press F1 in Excel and read the builtin tutorials to see all the capabilities of any one function.

So, you'll need a different function. INDEX/MATCH can be tricked into advancing matches if your source data is properly sorted like it is in your sample. This formula in B2 copied down gives the results you want from that data:

=INDEX(INPUT!$B:$B, MATCH($A2, INPUT!$A:$A, 0)+COUNTIF($A$1:$A2, $A2)-1)

This is a standard formula, not an array.

Hi benishiryo

Thanks for reply but your array formula returnig only Bombay and #NUM!
.

Hi,JBeaucaire

Great,your really rocket scientist thanks u vey much.
Good Day