Using Index/Match to Look for a Single Value in Multiple Sheets

[gsaxton91]

Hello, Im trying to use index match to auto populate material from one sheet, but I would like it search an additonal Old sheet if the current sheet doesnt have to data needed.
I've tried a few different methods here and cant really get it to populate data for both, its usually just one or the other.

=IF(D13>1,INDEX(C:C,MATCH(D13,'Old Sheet'!D:D,0)),IF(D13>1,INDEX(C:C,MATCH(D13,D:D,0))," NO "))
=INDEX(C:C,MATCH(D13,'Old Sheet'!D:DD:D,0))
=INDEX(C:C,MATCH(D13,('Old Sheet'!D:D),0),MATCH(D13,D:D,0))

Anyone have any ideas?

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[gsaxton91]

Sorry, Here is the attachement!

[gsaxton91]

I've tried using all of the below variations and have had no luck:

=INDEX(C:C,MATCH(D5,(WASHROOM!D:D)*('Old Sheet'!D:D),0))
=INDEX(C:C,MATCH(D6,('Old Sheet'!D:D)*MATCH(D6,D:D,0)))
=INDEX(C:C,MATCH(D5,(WASHROOM!D:D)*(MATCHOld Sheet!D:D),0))
=INDEX(C:C,MATCH(D9,D:D,0),1),INDEX(C:C,MATCH(D9,'Old Sheet'!D:D,0),1)
=IFERROR(INDEX(C:C,MATCH(D9,D:D&'Old Sheet'!D:D,0)),"")
=IF(D11<>"",INDEX(C:C,MATCH(D11,'Old Sheet'!D:D,0)),IF(D11<>"",INDEX(C:C,MATCH(D11,D:D,0))," NO "))

[Gregb11]

Sorry, I don't understand. You don't mention what value you're trying to get and where you're trying to put it (what cell).
Are you trying to get the Prod Code based on the Job #? Are you looking to put this formula in Col. F?
In your example you should show exactly what the EXPECTED answer is, and in which cell based on what rules.

[gsaxton91]

I've attached a more simplied verison of the excel sheet and what im trying to do.
Here is what im trying to do:
I am trying to Index/Match ****=INDEX(B:B,MATCH(C3,C:C,0))****, This would give me the value from the first sheet. Then this Index/Match ****=INDEX('Old Sheet'!B:B,MATCH(C3,'Old Sheet'!C:C,0))**** would give me the value from the second sheet.
What im trying to accomplish is to combine them.
I want the Index/Match to look into the Old sheet for the value put into the Column C, and match into Column B. If it cannot locate the value in the Old Sheet, I am trying to make excel then search the current sheet WASHROOM for the value. If it cannot find the value in either sheet, Im trying to leave the cell blank or "".
Does this help explain what im trying to do?

[AliGW]

The problem seems to be that you are trying to combine hard coded values and formulae in the washroom sheet - you can't do that. A cell can contain either a formula or a value, but not both.

[Gregb11]

Does this help explain what im trying to do?

Not Really.
In your sheet, B2 has an actual value (not a formula), but the ones below it do have a formula. Is that the way it's going to be?
This formula
=INDEX(B:B,MATCH(C3,C:C,0))
says, "get the value from Column B by going down the same amount of rows where C3 matches a value in COL C.

I think you'll end up with a circular reference when there isn't a match. Say you're on the 5th row and there's no match in the "Old Sheet", it will then find the value (itself) in row 5, so it will try to bring back the value in the 5th row in column B which is the row where the formula is.

[gsaxton91]

Ok, I'm trying create an auto fill system after the data has already been entered.

Row 1: Will have the data manually entered in.
The following rows: 2- 10,000 will have a formula.

I'm trying to have the formula index/match look for the value in the Old Sheet first. If it can't locate that information in the old sheet, it will then Index/match in the current sheet.

I'm having trouble combining the two index/matches. Is that possible?
Combine : ( =INDEX(B:B,MATCH(C3,C:C,0)) and =INDEX('Old Sheet'!B:B,MATCH(C3,'Old Sheet'!C:C,0)) ) into one formula.

[Gregb11]

Well, maybe you could try something like this:
=IF(ISNUMBER(MATCH(C3,'Old Sheet'!C:C,0)),INDEX('Old Sheet'!B:B,MATCH(C3,'Old Sheet'!C:C,0)),INDEX('Old Sheet'!B:B,MATCH(C3,'Old Sheet'!C:C,0)))

[AliGW]

If this is solved, please say so here and share the solution,. We do NOT delete threads, as they may be helpful to others later.

[gsaxton91]

I solved this on my own using two separate columns:

Please Login or Register  to view this content.