Is it possible for Excel to use 2 factors to find a cell? For example:
Excel Example.jpg
I want to find and display B2 by using Name 1 and Variable 1, or display E3 by using Name 2 and Variable 4. The names and the actual names of the variables will change through different spreadsheets, they are going to be referenced to cells on another worksheet.
Try INDEX/MATCH
e.g.
=INDEX(B2:E6,MATCH("Name 2",A2:A6,0),MATCH("Variable 4",B1:E1,0))
replace Name 2 and Variable with cell references if desired.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
That looks like it's doing exactly what I need it to, thanks!!! Now time to customize it....
How can I combine that whole phrase with an OR statement, to have it look on 2 different worksheets?
Try:
where Sheet1 and Sheet2 are the sheetnames.
=IF(ISNA(INDEX(Sheet1!B2:E6,MATCH("Name 2",Sheet1!A2:A6,0),MATCH("Variable 4",Sheet1!B1:E1,0))),INDEX(Sheet2!B2:E6,MATCH("Name 2",Sheet2!A2:A6,0),MATCH("Variable 4",Sheet2!B1:E1,0)),INDEX(Sheet1!B2:E6,MATCH("Name 2",Sheet1!A2:A6,0),MATCH("Variable 4",Sheet1!B1:E1,0)))
Okay, I have made the following changes, which Excel does not like:
Can you see what I have done wrong?=IF(ISNA (INDEX(‘Not Ready 1’!$C$2:$Z$300,MATCH ($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH (D$2,'Not Ready 1'!$C$1:$Q$1,0))),INDEX ((‘Not Ready 2’!$C$2:$Z$300,MATCH ($C4,'Not Ready 2'!$B$2:$B$300,0),MATCH (D$2,'Not Ready 2'!$C$1:$Q$1,0)),INDEX (‘Not Ready 1’!$C$2:$Z$300,MATCH ($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH ("D$2,'Not Ready 1'!$C$1:$Q$1,0)))
Also, if I want to get rid of the N/A by making them a dash '-' I usually do something like this:
With your ISNA formula, would I still be able to do this too?=IF(ISERROR((INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C3,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0)))),"-",((INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C3,'Not Ready 1'!$B$2:$B$300,0),
Try it like this:
=IF(ISNA(INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))),INDEX('Not Ready 2'!$C$2:$Z$300,MATCH($C4,'Not Ready 2'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 2'!$C$1:$Q$1,0)),IF(ISNA(INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))),"-",INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))))
That keeps giving me an answer of .125 no matter where I paste it?
What do you mean by no matter where I paste it?... the formula is going to reference the same cells if you are pasting it about.
You need to enter it once, then copy it down if you want references to change from C4 to C5, etc...
Sorry I didn't even catch that I pasted into a different area then what I sent you (hence screwing up the referneces), I'm making the changes now to see what happens... I've been working on this for hours and my head is starting to hurt lol.
Yeah, even with the changes to the reference cells, I still keep getting a value of .125 returned. I have verified by clicking in the forumla and checking the colored boxes are highlighting the correct cells also.
It should be looking at cells C4 and D2 for the criteria.=IF(ISNA(INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))),INDEX('Not Ready 2'!$C$2:$Z$300,MATCH($C4,'Not Ready 2'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 2'!$C$1:$Q$1,0)),IF(ISNA(INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))),"-",INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))))
This formula worked:
But it's only taking into account data from Not Ready 1.=IF(ISERROR((INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0)))),"-",((INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0)))))
Last edited by Sponge14; 06-13-2012 at 03:36 PM.
Can we see the workbook (or cleaned sample)?
Hmm. Yeah that will take me a few mins to get rid of confidential info, but yeah, I think at this point that will be the best bet....
Is there a way I can Email it instead of posting here?
Okay, I have fixed what you told me to in that Email about a dozen times, I was sure of it! So now, I fixed it again, and all fields are populating from both NR1 and NR2. All is good. So, I delete the data from NR1 and NR2 and I do not touch the formula on the Summary page. I then copy and paste new data into NR1, and it's not working again!!!! I checked my formula, and it has again changed to the incorrect column letters!!!!! Excel is changing my formula on me! What the hell is this thing doing???? Why would it change the formulas from working to not working like that??
Yup, I confirmed it again. By simply deleting the data in NR1 and NR2, then pasting the data back, my formulas are indeed changing every time. It's also not pulling the right data either... I think I'm pretty much giving up on this at this point....
Okay, I reviewed the formula again...
Try this one:
we prefer to keep samples in the forum for benefit of others seeking similar solutions.=IF(ISNA(INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0))),IF(ISNA(INDEX('Not Ready 2'!$C$2:$Z$300,MATCH($C4,'Not Ready 2'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 2'!$C$1:$Q$1,0))),"-",INDEX('Not Ready 2'!$C$2:$Z$300,MATCH($C4,'Not Ready 2'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 2'!$C$1:$Q$1,0))),INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0)))
That didn't work either... When I attach the spreadsheet to here will it copy my Macros? I don't want those to get out and they have no bearing on this issue...
Make a copy of the workbook, then delete the macros and attach that one.
It should be attached in my post above now...
I didn't realize you were returning time values... you need to format the result cells in the formula cells as TIME.
I would have sworn I did that, but I did again, and it looks like it's working now.... I can't believe it was something that stupid that was doing me in!!! Thanks alot, you're awesome!
Unfortunately it looks like I was premature with the happiness... The data is pulling correctly from Not Ready 1, but it's not pulling from Not Ready 2, so it's about half way there. I have made some changes to try to make the formulas that are not working, to make them look like the Not Ready 1's which are working, but obviously I am still stumped....
I copied and pasted the formula in 2 parts right on top of one another, and they are identical except the one says Not Ready 1, and the other is Not Ready 2... It's like the formula is just ignoring the Not Ready 2 search.
Okay, and I did another experiment... I took my modified formula and pasted it into a cell that was pulling from Not Ready 1 correctly, and it kept pulling correctly, so for some reason it's not searching Not Ready 2.
Last edited by Sponge14; 06-13-2012 at 07:06 PM.
After even more experimentation, I have found if I break out the Not Ready 2 search like
It will return the correct information. So it is definitely when I combine the Not Ready 1 search with it.=INDEX('Not Ready 2'!$C$2:$Z$300,MATCH($C14,'Not Ready 2'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 2'!$C$1:$R$1,0))
I am not sure what you mean. In the attached, Melissa Doe is on Not Ready 2... and her info is being pulled up properly in Metrics page (i.e. D11).
Yup, but when I put it into the actual spreadsheet, then all of the sudden it stops working.... I just can't see a difference in the one that you just posted and my real sheet. This is insane now, really.... At this point I'm clocked out, and I think after working for 8 hours it's time for a break. I'll take a fresh look at it tomorrow and get back on here if I am still having problems... I really can't express how much I appreciate the help you have provided me!Originally Posted by NBVC
check for spelling inaccuracies, extra spaces in the cells that should be matching up, etc..
I am not sure what's going on, but this is borderline insane... The formula works. However, if I have the formula already pasted in all the cells, then I drop the data into the 2 Not Ready worksheets, the formula does not pull any of the data. However, if I copy and paste the exact same, working formula into the cells again then the data shows up correctly. I do not use the option to where I have to force a recalc, they should be recalculating as soon as I drop the data into the Not Ready worksheets....
Last edited by Sponge14; 06-14-2012 at 01:49 PM.
I also realized that I was pasting my data to Excel in HTML format and thought that had something to do with it. I tried pasting without any formats, but this too did not help.
if you are using 2007 like your profile states this is an alternative formula:
does that one do any better?=IFERROR(INDEX('Not Ready 1'!$C$2:$Z$300,MATCH($C4,'Not Ready 1'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 1'!$C$1:$Q$1,0)),IFERROR(INDEX('Not Ready 2'!$C$2:$Z$300,MATCH($C4,'Not Ready 2'!$B$2:$B$300,0),MATCH(D$2,'Not Ready 2'!$C$1:$Q$1,0)),"-"))
Note that these formula look in Sheet1 first and only go to sheet2 if not found in sheet1.
Okay so here's exactly what I just did:
-I pasted my data into Not Ready 1 and formatted it to the way it needs to be (this was OK)
-I pasted your new formula and edited the cells to match up for where I pasted it: That cell populated with the correct data
-I pasted this correct formula to the most top left cell, again, it populated data
-I highlighted all the cells that need this formula, again, they all populated correctly
-Now, I pasted my data into the Not Ready 2 sheet, but this time all the employees that were not on NR1, but are on NR2, do not populate their data.
Any ideas with this new info?
There's now way you can upload this workbook?
If not, PM me and I will give you my email address and will have a look.
I just tried copying the NR1 and NR2 data to sheet1 and sheet2, then deleted the data from NR1 and NR2 and then re-copied and pasted the data back with no issues.
Are you by any chance running any of the macros in between? I had the macros disabled. Try repeating the process without macros enabled... I don't know what else is going on...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks