Lookup and retrieve value based on table code

[Lux Aeterna]

Hello, everyone!

I need to find the value in cell B6 within the sheet specified by the code in cell O2, which corresponds to sheet Table D16.

The function should search for the value in B6 within column D of this sheet, and once it finds a match, it should return the corresponding value from column C.

I can move column C to the right, if it's simpler for you.

Thank you in advance!

PS. Some cells in the Table sheets include a range rather than a single value. For example, D81. Would it be possible to search for values in these cells?

 

[Peter_SSs]

Try something like this. It may need confirmation with Ctrl+Shift+Enter, not just Enter in your excel version.

=INDEX(INDIRECT("'"&VLOOKUP(O$2,M$2:N$21,2,0)&"'!C:C"),MATCH(B6,--LEFT(INDIRECT("'"&VLOOKUP(O$2,M$2:N$21,2,0)&"'!D:D"),FIND("-",INDIRECT("'"&VLOOKUP(O$2,M$2:N$21,2,0)&"'!D:D")&"-")-1),1))

Note though that INDIRECT is a volatile function and may slow your sheet performance, particularly if used a lot on the worksheet.

 

[Lux Aeterna]

Thank you! The formula works perfectly overall. However, I'm encountering an issue with specific cells marked in red in the uploaded file. It seems that the problem might be related to the cell format (e.g., General, Text, Number, etc.) in the D* sheets. When I pasted the tables from my OCR application, some of the values were mistakenly converted to dates, and I had to manually correct them. This may be affecting the formula in those cells.

The formula fails to look up
- the value in cell B16 within the D* sheet, specifically in the L7:L99 range.
- values in cells B19-B22 within the D* sheet, specifically in the O, P, Q, and R7:R11 ranges respectively.

Could you advise on how to resolve this issue?

XL2BB causes my files to crash, so I've uploaded the files directly instead.

If there's anything password protected, you can try 299

 

[Peter_SSs]

The formula fails to look up
- the value in cell B16 within the D* sheet, specifically in the L7:L99 range.
- values in cells B19-B22 within the D* sheet, specifically in the O, P, Q, and R7:R11 ranges respectively.

I presume you meant B15 not B16 since B16 is empty.
The formulas are not working because the format of your data is different to the sample you originally provided.
In the original sample, in column D of 'Table 16' ...

1. There are no empty cells within the data, and
2. The values/ranges are increasing

In your sample workbook in column L & Q of 'D1' ..

1. There are empty cells within the data, and
2. The values are decreasing

In column R the data is clearly in a different format using "to" between the values instead of "-" (& there was no mention or examples with negative numbers in your original post)

You mentioned that the formula in row 20 was not returning the correct result but it looks correct to me & that occurs because the values in column P of 'D1" are increasing and with no blank cells as per the original question sample data.

There is no easy change to my formula that I am aware of to account for the differences in the other columns.

 

[Lux Aeterna]

Thank you for the clarification. I wasn't aware that the data's direction could affect the formula. I was able to resolve the issue by copying and pasting columns A, B, C and L into different locations, then reversing the data.

However, I noticed that blank cells in columns E-K don't seem to affect your formula; it still works perfectly, at least with the random data I checked.

I'll mark this thread as solved, and if I don't manage to modify your formulas appropriately, I'll start a new thread.

 

[Peter_SSs]

Glad you were able to get something working.