How to vlookup from one sheet to answer another sheet when data is not in ascending ordr

[devawad]

Hi all,

HOW TO VLOOKUP FROM ONE EXCEL SHEET TO (ANSWER) ANOTHER SHEET WHEN DATA IS NOT IN ASCENDING ORDER,(OR USING INDEX MATCH FUNCTION).

I HAVE ATTACHED SAMPLE FILE FOR THE REFERENCE...IN THAT

IF IN STOCK DETAILS FILE COLUMN D HAS PABW040804(MEANS CELL C5 in sheet1)SO ANSWER SHOULD COME FROM COLUMN H FROM STOCK DETAILS FILE(MEANS QTY/PCS)

KINDLY DO THE NEEDFUL
THANKS IN ADVANCE

Dev.

[FDibbins]

If I understand you correctly, you want to find Code PABW0408040 (from sheet1 C5) and search for that code in Stock Details sheet, and the return the value in column Hon that sheet.

I cannot find PABW0408040 on any other sheet, which sheet and column is it supposed top be in?

[devawad]

Dear sir,

I want to search the code exactly which is in sheet1 C5 from sheet "stock details" if it found get the PCS from the H column. for example this time you consider cell 15 in shee1.

If code PABW0607040 is available in column C in "STOCK DETAILS" answer should be 20 from column H (cell H15) from sheet stock details, in cell E13 of sheet1.

If code is not exist keep cell blank.

kindly help

thanks in advance.

Dev

[FDibbins]

Try this, copied down...
=IFERROR(VLOOKUP(C5,'Stock Details'!$C$3:$C$117,6,0),"")

But I could not find any matches in your data

[devawad]

Sir
thank so much for trying for me but
Its not working as I want..

Dev

[devawad]

Hi all,

=OFFSET('Stock Details'!$C$1,MATCH("PABW0608040",'Stock Details'!$C$2:$C$114,0),5)

I am using the formula above which works just fine, however it returns a #N/A when the criteria do not match

I would like the formula to return a blank cell.

plz help

Dev.

[FDibbins]

Sir
thank so much for trying for me but
Its not working as I want..

Dev

what is it doing? saying what you did does not help us to fix it, and I did tell you I could not find any matches in your data?

If =OFFSET('Stock Details'!$C$1,MATCH("PABW0608040",'Stock Details'!$C$2:$C$114,0),5)
is working for you, wrap it in IFERROR() to error-trap...
=IFERROR(OFFSET('Stock Details'!$C$1,MATCH("PABW0608040",'Stock Details'!$C$2:$C$114,0),5),"")