Simple v look up formula is not working

Hi all,

A18:A37 is my look up, from which I want to find out the qty from the range of D2:D14. Only one item is found in the look up, as I can see there is many other items should be found.

Please guide me is there any other things r missing in my format or formula. I have tried lot changing format and even tried with index match. sorry I am still newbie  at this. It is a very frequent problem I face that the criteria remains, but doesn’t show.

In cell G18, change your formula to:

Formula:

Please Login or Register  to view this content.

Then copy down. Your current formula uses relative addressing, so when you copy it down it changes the row number. If you put a $ it will not change the row numbers when you copy down.

Thanks Jazzer. Seems i need to learN $ uses. but can u tell me what formula i should use if i want to do it by Index match formula in case of this document ? will be highly appreciated.

Maybe this>
=INDEX($D$2:$D$14,MATCH(A18,$A$2:$A$14,0))

=IFERROR(VLOOKUP(A18,$A$2:$D$14,4,0),"")


=IFERROR(INDEX($D$2:$D$14,MATCH(A18,$A$2:$A$14,0),1),"")

These will also hide errrors

Hi All,

looking for quantities

Please Login or Register  to view this content.

could be useful too,

Regards

And you can also use OFFSET function

=IFERROR(OFFSET($A$1,MATCH(A18,$A$2:$A$14,0),3),"")


And INDIRECT


=IFERROR(INDIRECT("D"&MATCH(A18,$A$2:$A$14,0)+1,3),"")

Hi ALKEY,

currently using 2003 version. can you advise me how to place if error in excel 2003 ? it that ifi(Isna ?

Hi FDIBBINS
thanks it is solved.thnks

Hi canapone,

wow, learned in another ways.Thnks.

For 2003


=IF(ISERROR(VLOOKUP(A18,$A$2:$D$14,4,0)),"",VLOOKUP(A18,$A$2:$D$14,4,0))


=IF(ISERROR(INDEX($D$2:$D$14,MATCH(A18,$A$2:$A$14,0),1)),"",INDEX($D$2:$D$14,MATCH(A18,$A$2:$A$14,0),1))

Happy to help and thanks for the feedback

Thanks Alkey.

Hi FDibbins,
this is site is helping me to grow up with excel. thanks to all u guys.