Searching multiple columns

Hi.

An actual workbook would be much preferable to a picture or two.

Regards

Thanks.

There does not appear to be a space in your entry in cell F2 (Value32), though there is in that in cell B12 (Value 32), which will obviously need to be rectified so that this solution will work.

Assuming that each entry in the range A2:C31 can only ever occur once, in G2:

=INDEX($D$2:$D$31,SUMPRODUCT(($A$2:$C$31=F2)*(ROW($A$2:$C$31)-MIN(ROW($A$2:$C$31))+1)))

Copy down as required.

Regards

Thanks, that's very helpful. However it seems to return the first value if that ID is not located in the list or provide me a #REF. I've attached a more appropriate workbook now that I've got access to my computer. So it will search for the ID in column H through columns A-E and return the corresponding value in column F. By the way there will be duplicates within this list so if it does see a duplicate or the ID is not found then return a N/A.

Cheers

I should also add this would also be applied to search those IDs and return text. Would much tweaking need to be applied to the formula to return text?

Ah yes. Apologies. I did not consider cases where the search value was not found anywhere.

My previous formula should be:

=IF(COUNTIF($A$2:$C$31,F2),INDEX($D$2:$D$31,SUMPRODUCT(($A$2:$C$31=F2)*(ROW($A$2:$C$31)-MIN(ROW($A$2:$C$31))+1))),"Not Found")

I trust you can adapt this to work for the set-up in your new workbook?

It does not matter if the values in the column to be returned are text or numeric using this solution.

Regards

Also array formula to prevent duplicates

=IF(OR(ROWS(F$2:F2)>COUNTA(F:F)-1,COUNTIF(A$2:C$31,F2)>1),"N/A",MAX(IF(A$2:C$31=F2,ROW(A$2:C$31)-1),0))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER