LinkBack URL
About LinkBacks
Hi,
Need a Two Way lookup formula using Index Match function.
An excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
Hi,
Need a Two Way lookup formula using Index Match function.
An excel file attached with necessary details.
Pl. help.
Thanks,
Nagesh.
In D3:
=INDEX($K$3:$T$12,MATCH(1,($I$3:$I$12=A3)*($J$3:$J$12=B3),0),MATCH(C3,$K$2:$T$2,0))
Array enter with CSE, then copy down.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Cell D3 formula , Drag down
Formula:
Please Login or Register to view this content.
try
=INDEX($J$3:$T$12,MATCH(A3,$I$3:$I$12,0),MATCH(C3,$K$2:$T$2,0))
I have used ROLE ID as i assume that would be unique to the individual
if the cell is blank it will return a zero
we can remove that with an IF
=IF(INDEX($J$3:$T$12,MATCH(A3,$I$3:$I$12,0),MATCH(C3,$K$2:$T$2,0))=0,"no Entry",INDEX($J$3:$T$12,MATCH(A3,$I$3:$I$12,0),MATCH(C3,$K$2:$T$2,0)))
Or just leave the cell blank
=IF(INDEX($J$3:$T$12,MATCH(A3,$I$3:$I$12,0),MATCH(C3,$K$2:$T$2,0))=0,"",INDEX($J$3:$T$12,MATCH(A3,$I$3:$I$12,0),MATCH(C3,$K$2:$T$2,0))
Last edited by etaf; 04-15-2024 at 08:55 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Wayne, as it is text values that we are retrieving, you can just add &"" to the end of your first formula to avoid the zero if blank - avoids having to do the lookup twice.Originally Posted by etaf
Hope this helps.
Pete
wow - thats cool
=INDEX($J$3:$T$12,MATCH(A3,$I$3:$I$12,0),MATCH(C3,$K$2:$T$2,0))&""
thanks
never knew that - will save loads of lookups on various forums where i have suggested the IF()
Thanks again, for taking the time to advise
Last edited by etaf; 04-15-2024 at 08:54 AM.
A related tip is for when numbers are being retrieved, and zero is not a valid value - you can do this:
=IFERROR(1/(1/lookup_formula),"")
I first saw that one from Biff (Tony Valko), who no longer contributes here.
Pete
thanks for the follow-up , as was about to ask, but had to go hunting wifes phone - which she dropped ...... while out
Thank you @Ali, wk9128, Wayne and Pete its working fine.
Glad to have helped.
If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks