Formula for lookup with data varying in column

[excelhelpguy123]

Hello,

I am struggling to find a formula that works. I am taking data from an external source and sometimes the data will be off one column and I do not want to have to edit the raw data to find what I need. I lookup a word within a specific column and find a number based on how far away that data is from that word. It is the same distance every time except for when the raw data is off a column. I have attached an example.

Thanks in advance.

[mcmahobt]

Based on the formulas you have, a little more explanation is needed on your end. Your current formula is matching "XX-1" and "XX-2" in column G. The first match is found, but the second match yields no results since there is no "XX-2" string in column G. Is this expected?

[JBeaucaire]

The function you probably want is INDEX/MATCH/MATCH

=INDEX(table _of_values, RowNmber, ColumnNumber)


So, I copied your table2 to another sheet so it sits in the top left of the sheet starting in A1. Then this formula tells you what value is for Team C, column XX

=INDEX($1:$1048576, MATCH("c",$A:$A, 0), MATCH("XX",$1:$1, 0))

[excelhelpguy123]

This is not expected. In a perfect world it would match XX-1 and XX-2. But since there is no XX-2 it does not return anything. I would like for it to return the value under XX for Team Two. I think the way I went about it was not the ideal way. I did not expect to have to deal with a column moving.

[mcmahobt]

Not elegant by any means, but since you're not able to easily match on both row and column references, a brute-force array does seem to get the trick done, albeit clunky.

Test this in J4, entered as an ARRAY FORMULA and confirmed with CTRL+SHIFT+ENTER:

Please Login or Register  to view this content.

Copy down to fill.

[excelhelpguy123]

Here is a better representation of my data set. It contains three tabs. There is a team name, for example the Kansas City Comets, and then players listed underneath. I would like the height and weight of the first player for each team to be listed in a table. Sometimes the team names get misaligned so there is not a set format for each one. I am running a macro through each one so I need a set formula.

[JBeaucaire]

Another take on INDEX/MATCH/MATCH

J6: =INDEX($A:$G, MATCH(I6,$A:$A, 0), 3-ISNUMBER(MATCH("Height",$B:$B, 0)))
K6: =INDEX($A:$G, MATCH(I6,$A:$A, 0), 4-ISNUMBER(MATCH("Weight",$C:$C, 0)))