What wrong in array formula-vlookup & Match

[paradise2sr]

Dear Sir,

Pls find enclosed in attachment a sample excel.

I have a problem that I want value in 3rd column looking the criteria of 1st column and 2nd column and filling the value in 3rd column from next sheet picking up value from there.

Can anybody help in this regard.

Thanks in advance.

With Regds,

Suresh

[FDibbins]

1st, you seem to have calcs set to manual (change under Formulas/Calculation)
then tr this regular formula, copied down...
=VLOOKUP(B3,Data!$B$2:$G$7,IF(C3="S",3,6),0)

[FDibbins]

If you want to use your version, change it to this regular formula...
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(C3&"-Value",Data!$B$1:$G$1,0),0)
or
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(C3&"*",Data!$B$1:$G$1,0),0)

In your version...
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(Sheet1!C3,Data!$C$2:$C$7,0)+5,0)
the MATCH is trying to find S in the 2nd row. but even if you were looking in the 1st row, there is no S, only S-Value

Hope this helps?

[AlKey]

This formula will work too:

=IF(C3="S",OFFSET(Data!$B$1,MATCH(B3,Data!$B$2:$B$7,0),2),OFFSET(Data!$B$1,MATCH(B3,Data!$B$2:$B$7,0),5))

[paradise2sr]

Dear Sir,

Thanks for your prompt reply.
All your formula gives me the desired result,except last formula-
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(Sheet1!C3,Data!$C$2:$C$7,0)+5,0)

Any how thanks very much.

With Regds,
Suresh

If you want to use your version, change it to this regular formula...
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(C3&"-Value",Data!$B$1:$G$1,0),0)
or
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(C3&"*",Data!$B$1:$G$1,0),0)

In your version...
=VLOOKUP(B3,Data!$B$2:$G$7,MATCH(Sheet1!C3,Data!$C$2:$C$7,0)+5,0)
the MATCH is trying to find S in the 2nd row. but even if you were looking in the 1st row, there is no S, only S-Value

Hope this helps?

[FDibbins]

Happy to help, and by the way, that last formula was the 1 you showed us in your file

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.