Index, match whatever???

[John Wilson]

Having some brain farts with this one....

If anyone has a moment to steer me in the right direction.....

My lookup table (RangeB5:C8):
(row) B C
5 1 4
6 2 3
7 6 7
8 5 8

My results table (what it should look like)
Column A is static 1 thru 8
Column B should show the mate from Range (B5:C8)

(row) B C
14 1 4
15 2 3
16 3 2
17 4 1
18 5 8
19 6 7
20 7 6
21 8 5

My formula (so far) in column B:
=IF(ISNA(VLOOKUP(B14,$B$5:$C$8,2,FALSE)),"problem",VLOOKUP(B14,$B$5:$C$8,2,F
ALSE))
The above works if the lookup was found in Column B.
I know that I need an INDEX/MATCH for the "problem", but I can't seem to get
it to work.
Basically, I'm looking up the numbers 1 thru 8 in B5:C8
If found, return the number to the right of where it was found.
If not found with the VLookup, it has to be found in Column C
but I need to return it's mate from Column B

Thanks,
John

 

[Peo Sjoblom]

Hi John,

this will do it

=IF(ISNUMBER(MATCH(B14,$B$5:$B$8,0)),VLOOKUP(B14,$B$5:$C$8,2,0),INDEX($B$5:$
B$8,MATCH(B14,$C$5:$C$8,0)))

copied down returns the same as your C14:C21 although you might want to
guard against if there is no match at all

=IF(COUNTIF($B$5:$C$8,B19)=0,"Problems",IF(ISNUMBER(MATCH(B19,$B$5:$B$8,0)),
VLOOKUP(B19,$B$5:$C$8,2,0),INDEX($B$5:$B$8,MATCH(B19,$C$5:$C$8,0))))

would do that

HTH

 

[John Wilson]

Peo,

That worked like a charm.
I just couldn't seem to get it right.
Thanks for the help.

guard against if there is no match at all

For this project, there will always be 1 thru 8,
but I'll save option 2 in my archives.

Thanks again,
John