Trouble with Index(match) - I think!

[n3mcx1]

I am struggling to figure this out, and am turning to the gurus for assistance.

I have a sample workbook I have attached which hopefully helps.

Essentially, I have two pages, an input sheet and a load sheet with various random data used to load into a 3rd party system.

The input sheet contains various info and is used to fill in the load sheet.

In this example, I am trying to get #Load-Location!F5 to match the name in I2:I7 on the #Input-Location IF and ONLY IF one of the cells in B2:B7 is unhashed(Blank). At the same time, the info in #Load_location!F5 is concatenated with #Load_location!H7.

I can only get it to work if I leave the "#" in the formula I currently have in #Load-Location!F5 (=INDEX('#Input-Location'!I2:I7,(MATCH("#",'#Input-Location'!B2:B7,0))), if I try to make the MATCH(""....) I get a #N/A returned.

Hopefully someone smarter than I can figure out a solution.

I thank you all in advance.

v/r

Tim

[hemesh]

In this example, I am trying to get #Load-Location!F5 to match the name in I2:I7 on the #Input-Location IF and ONLY IF one of the cells in B2:B7 is unhashed(Blank). At the same time, the info in #Load_location!F5 is concatenated with #Load_location!H7.

I can only get it to work if I leave the "#" in the formula I currently have in #Load-Location!F5 (=INDEX('#Input-Location'!I2:I7,(MATCH("#",'#Input-Location'!B2:B7,0))), if I try to make the MATCH(""....) I get a #N/A returned.

Tim

If i am not wrong you are trying to match blank cell in B2:B7 and want to return Corresponding cells value from I2:I7, if so than you can use
copy paste below then hold control and shift together and then hit enter.
=IFERROR(INDEX('#Input-Location'!I2:I7,MATCH(TRUE,'#Input-Location'!B2:B7="",0)),"")
if there are no blanks in B2:B7 then above formula will remain blank.

[n3mcx1]

Thanks Hemesh.

This worked well for my sample spreadsheet, but when I went to put it the real spreadsheet it didn't work at all. I think I have some cell formatting issues, and am investigating further.

In principal, it should work both places.

Thanks again for your help.

tks,

Tim