Help with VLOOKUPs on non-sorted data

[lankks]

Hi there,

I basically want to perform a VLOOKUP, but with it associating the values to different columns. So I want the ID column in Sheet 2 to match up with any of the values between W1 and W5, then give me the result of the tank number in Sheet 1. I think the problem is the numbers in W1 to W5 aren't necessarily in order, and excel can't handle it. I would change them, but it can't be arranged in any other way because each W value associates to a cup with a weighted sample.

Anyway, I hope that made some sense, and I'd really appreciate the help ASAP.

[estige]

Hi, the IDs in sheet 1 and 2 are not the same: "SP" in front of the ID in one of the sheets. Remove "SP" in sheet one ore add it to the ID numbers in sheet 2.

[Haseeb Avarakkan]

Hi, try this

Sheet2!F2, with CTRL+SHIFT+ENTER, rather than just ENTER

=IFERROR(INDEX(Sheet1!$C:$C,SMALL(IF(Sheet1!F$2:J$13=A2,ROW(Sheet1!F$2:F$13)),1)),"")

Copy down

[lankks]

Hi, try this

Sheet2!F2, with CTRL+SHIFT+ENTER, rather than just ENTER

=IFERROR(INDEX(Sheet1!$C:$C,SMALL(IF(Sheet1!F$2:J$13=A2,ROW(Sheet1!F$2:F$13)),1)),"")

Copy down

That didn't work, it just copied the first few entires in the tank column out verbatim and didn't associate the ID with the any of the W columns. Was I meant to copy paste the formula in as is?

And I can't change the ID's in sheet 1, because they represent individuals, and the W number represent the cups I used to weigh a sample of them. So they're different things.

EDIT: Actually, it did, I just misunderstood what you were saying. Could you explain this formula to me, because I need it to stretch over a much larger sheet and for a few more columns.

[estige]

Then try:
=VLOOKUP("SP"&A2,Sheet1!$A$1:$J$13,3,FALSE)