Formula to read an specific column based on another cell value

[LEORITY]

Hi guys,

As part of a formula I am building, what I need is when I type an Australian State in a cell, the formula reads the column correspondent to that State. I have attached the spreadsheet as an example.

I tried a mix of VLOOKUP and HLOOKUP without success...

Please let me know you expert thoughts

Thanks in advance.

[southward]

Hi,

With a list modification of your C2 cell to just the state abbreviation you can use this INDEX MATCH MATCH formula.

Please Login or Register  to view this content.

Attached is a modification of your file showing how it works.

Hope this makes sense and works for you.

Cheers

[LEORITY]

Hi there,

Thanks for your reply. I guess I over complicated my example.

What I really need is a formula that, based on the value provided in cell C2, will change the column looked at.

The whole formula is:

{=IFERROR(INDEX(Forecast_Data_Entry!A:A,SMALL(IF(Forecast_Data_Entry!$C$3:$C$10000<>"",IF(Forecast_Data_Entry!$A$3:$A$10000>$L$89,ROW(Forecast_Data_Entry!$F$3:$F$10000))),ROWS($L$90:L90))),L89+1)}

The bold part of the formula is the bit I am missing. I want that section of the formula to provide a result that will look at a different column depending on the input from cell C2. At the moment I need to manually change it from Forecast_Data_Entry!$C$3:$C$10000 to Forecast_Data_Entry!$F$3:$F$10000 manually...

Does it make sense?

Apologies for not making it clearer earlier.

Thank you very much.

LEORITY

[samba_ravi]

what is your expected result in your example and why

[FDibbins]

Agree with Siva. You have shown us what you tried and did not work, but we need to see what you expect

[LEORITY]

I have a database on the same spreadsheet that will be read by this formula. On cell C2 there will be a typed value (in the example there are Australian states, but it can be other things too) and I need the formula to read any column where the typed value is.
I just need to change the bit that is in bold because the formula works well (apart that I have to manually change the column when things change).
Hope this made a bit clearer...
Cheers

[LEORITY]

I have just remade the example with the actual formula in it.

I want to type in cell C2 one of the locations (that will change names and number of locations over time).

Formula:
{=IFERROR(INDEX(H:H,SMALL(IF($I$6:$I$10000<>"",IF($H$6:$H$10000>$C$7,ROW($I$6:$I$10000))),ROWS($C$8:C8))),C7+1)}

At the moment I have to manually change the $I$6:$I$10000 part of the formula. So I just wanted a way to automate it based on what I type on cell C2.

Thanks

[samba_ravi]

C8=IFERROR(INDEX(H:H,SMALL(INDEX(((INDEX($I$6:$N$10000,,MATCH($C$2,$I$5:$N$5,0))="")+($H$6:$H$10000<C$7))*10^10+ROW($H$6:$H$10000),0),ROWS(B$8:B8))),"")

Please Login or Register  to view this content.

Try this and copy towards down

[LEORITY]

Hi thanks for this!!

I only modified a parts of the formula to suit my needs. See below:

=IFERROR(INDEX(H:H,SMALL(INDEX(((INDEX($I$6:$N$10000,,MATCH($C$2,$I$5:$N$5,0))="")+($H$6:$H$10000<=C$7))*10^10+ROW($H$6:$H$10000),0),ROWS(C$8:C8))),C7+1)

Can you please explain the indexes on this formula? Also, what does the 10^10 do?

Thank you so much for the great solution!!!

[samba_ravi]

to avoid shift+ctrl+enter I used Index then
if the condition not fulfill then 10^10 will give 10000000000, but excel will not have that much of rows, so simply it through an error after pulling the required data

[LEORITY]

WOW! That is impressive. Thanks once again!!