How can I combine VLOOKUP within an IF statement where the column index of the VLOOKUP function is a number extrapolated from a text?

Question

I have the following tables in Excel:

and

.

The product Stock table (PST) is more extensive than this but for the purposes of this question I have cut it down.

I want to lookup the value in a column in Stock Analysis table (SAT) matches the value in the Size column of the Product Stock table.

For example, in this case, the value in the Size Column of my Product Stock table (PST) is 8 so I want to look up the value in the Size 8 column of my Stock Analysis table. If my value were 5 then lookup Size 5 in my Stock Analysis table (SAT).

Note that data validation has been carried out to ensure that the values in the Size column of my Product Stock table are based on specified dimension hence will only have a range of sizes that are also the columns of the Stock Analysis table

Also formula has been inserted to ensure that the next row of the SAT will always have a batch number which is one more greater than the previous row (i.e. there is increase of 1 on the Batch No column for each new row) this is to ensure no repetition of batches in the SAT

What I have so far is:

=IF(PST[Batch No]=SAT[Batch No, VLOOKUP(PST[Batch No], PST, Stuck here, FALSE), "")

What I need on the col_index_num is to match the value in my Size column of my PST to the last character of the string in the headers of SAT excluding the Batch No header (this might not affect it though). When there is a match give the column number on the table.

This will then give the value under that column that matches the Batch No.

I hope this is fairly understandable.

I would very much not like to delve into VBA

Answer 1

Something like this?

Formula in H12 is:

=VLOOKUP(E12,$C$6:$H$8,MATCH("Size "&F12,$C$5:$H$5,0),FALSE)

Edit: How the formula in H12 works.

The part that provides a column number,

MATCH("Size "&F12,$C$5:$H$5,0)

first concatenates the prefix "Size " with the value of F12 (=8), resulting in a string "Size 8". Then it looks through the cells in the header row $C$5:$H$5 to find this key string and returns a number of the matching cell, namely, 6 (the last cell in the header). Then the formula

=VLOOKUP(E12,$C$6:$H$8,MATCH("Size "&F12,$C$5:$H$5,0),FALSE)

essentially becomes

=VLOOKUP(E12,$C$6:$H$8,6,FALSE)

which looks for the content of E12 (=1) in the first column of the range $C$6:$H$8. In other words, it selects the row, that corresponds to Batch No=1, which is 1. And given the row (=1) and column (=6) numbers in the range $C$6:$H$8, VLOOKUP returns a value stored in H6, which is 7.

Answer 2

Try the index function with a match for the Batch# and the Size text:

=INDEX(C6:H8,MATCH($E12,$C$6:$C$8,0),MATCH("Size "&$F12,$C$5:$H$5,0))